﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Collections;
using System.Configuration;
using System.Data.SqlClient;
using Freeview.Utility.Encryption;
using System.Threading;
using System.Net.Sockets;
using System.Net;
using System.Text.RegularExpressions;

namespace SqlExecuteHelper
{
    public partial class MainForm : Form
    {
        public int connectCount = 0;
        private MultiThread multiThread;
        public List<string> TenantConnectStringList { get; set; }
        public int CompleteTenantCount { get; set; }
        public List<string> SqlScripts { get; set; }
        public bool sqlExecuteError { get; set; }
        DataTable tenantTable { get; set; }

        public MainForm()
        {
            TenantConnectStringList = new List<string>();
            SqlScripts = new List<string>();
            CompleteTenantCount = 0;
            InitializeComponent();
        }

        private void MainForm_Load(object sender, EventArgs e)
        {
            UpdateServerSelector();

            this.tenantGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
        }

        //读取sql文本
        public List<string> LoadSqlFile()
        {
            //初始化一个OpenFileDialog类
            OpenFileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "*.sql|*.sql";
            fileDialog.Title = "请选择sql脚本";

            string sql = "";

            //判断用户是否正确的选择了文件
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                var fileName = fileDialog.FileName;

                this.txtSql.Text = Path.GetFileName(fileName);
                //直接读取出字符串
                sql = System.IO.File.ReadAllText(fileName, Encoding.Default);

            }
            else
            {
                this.txtSql.Text = "";
            }
            //以go分割，容错
            var reg = new Regex(@"(\n|\s)(go|Go|gO)\s", RegexOptions.IgnoreCase);
            sql = reg.Replace(sql, "\r\nGO\r\n");
            var aa = sql.Split(new string[] { "\r\n\r\nGO\r\n" }, StringSplitOptions.RemoveEmptyEntries).Where(s => !string.IsNullOrWhiteSpace(s)).ToList();
            return sql.Split(new string[] { "\r\nGO\r\n" }, StringSplitOptions.RemoveEmptyEntries).Where(s => !string.IsNullOrWhiteSpace(s)).ToList();
        }

        //选择脚本
        private void SqlSelector_Click(object sender, EventArgs e)
        {
            SqlScripts.Clear();
            SqlScripts = LoadSqlFile();
        }

        //log助手
        public void LogWriter(string log)
        {
            lock (this)
            {
                if (!Directory.Exists("./Log"))
                {
                    Directory.CreateDirectory("./Log");
                }
                using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"./Log/log.txt", true))
                {
                    file.WriteLine(log);
                    file.Close();
                }
            }
        }

        //获得tenant的连接字符串
        public string TenantConnectionString(string tenantServer, string tenantCode, string password)
        {
            tenantServer = tenantServer.ToLower();
            if (tenantServer.StartsWith(".") || tenantServer.StartsWith("localhost"))
            {
                //把. 或 localhost替换成127.0.0.1
                var reg = new Regex(@"^(\.|(localhost))");
                tenantServer = reg.Replace(tenantServer, "127.0.0.1");
            }
            var connectionStringFomart = "Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID=sa;Password={2};";

            password = DESHelper.Decrypt(password);

            return string.Format(connectionStringFomart, tenantServer, tenantCode, password);
        }

        //执行按钮点击事件
        private void Execute_Click(object sender, EventArgs e)
        {
            if (SqlScripts.Count == 0)
            {
                MessageBox.Show("请先选择要执行的脚本！");
                return;
            }

            if (MessageBox.Show(string.Format("【{0}】确定要执行这个脚本吗？", this.txtSql.Text), "确认", MessageBoxButtons.OKCancel) != System.Windows.Forms.DialogResult.OK)
            {
                return;
            }

            var dbName = this.DbSelector.SelectedItem.ToString();
            TenantConnectStringList.Clear();

            for (int i = 0; i < tenantGridView.Rows.Count; i++)
            {
                DataGridViewCheckBoxCell isUpdateCell = (DataGridViewCheckBoxCell)tenantGridView.Rows[i].Cells["IsUpdate"];
                DataGridViewCheckBoxCell state = (DataGridViewCheckBoxCell)tenantGridView.Rows[i].Cells["State"];
                if (Convert.ToBoolean(isUpdateCell.Value))
                {
                    if (Convert.ToBoolean(state.Value))
                    {
                       
                        TenantConnectStringList.Add(tenantGridView.Rows[i].Cells["ConnectionString"].Value.ToString());
                        TenantConnectStringList.Add(tenantGridView.Rows[i].Cells["ConnectionString"].Value.ToString());
                        TenantConnectStringList.Add(tenantGridView.Rows[i].Cells["ConnectionString"].Value.ToString());
                        TenantConnectStringList.Add(tenantGridView.Rows[i].Cells["ConnectionString"].Value.ToString());
                        TenantConnectStringList.Add(tenantGridView.Rows[i].Cells["ConnectionString"].Value.ToString());
                        TenantConnectStringList.Add(tenantGridView.Rows[i].Cells["ConnectionString"].Value.ToString());

                    }
                    else
                    {
                        TenantConnectStringList.Clear();
                        MessageBox.Show("有系统未通过测试，检查后重试！");
                        return;
                    }
                }
            }

            //开启多任务
            multiThread = new MultiThread(TenantConnectStringList.Count);
            multiThread.Process += new ProcessEventHandler(ExecuteScript);
            multiThread.Completed += new CompletedEventHandler(Completed);

            progressBar.Maximum = TenantConnectStringList.Count;
            progressBar.Value = 0;
            resultList.Items.Clear();
            CompleteTenantCount = 0;
            sqlExecuteError = false;

            multiThread.Start();
        }

        //脚本执行完毕方法
        public void Completed()
        {
            this.BeginInvoke(new Action(() =>
            {
                this.resultList.Items.Add("全部脚本执行完毕\r\n");
                this.resultList.SelectedIndex = this.resultList.Items.Count - 1;
            }));
        }

        //脚本执行
        public void ExecuteScript(int taskIndex, int threadIndex)
        {
            string tenantConnectionString = TenantConnectStringList[taskIndex - 1];
            if (string.IsNullOrEmpty(tenantConnectionString))
            {
                SetProcessBar();
                return;
            }
            SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder(tenantConnectionString);

            SqlConnection tenantConnection = new SqlConnection(tenantConnectionString);
            tenantConnection.Open();
            connectCount++;
            if (tenantConnection.State == ConnectionState.Open)
            {
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.Connection = tenantConnection;
                try
                {
                    foreach (var script in SqlScripts)
                    {
                        if (!string.IsNullOrWhiteSpace(script))
                        {
                            sqlCommand.CommandText = script;
                            sqlCommand.ExecuteNonQuery();
                        }
                    }
                }
                catch (Exception e)
                {
                    string log = string.Format("\r\n{0}  {1}脚本执行失败\r\n错误信息：{2}\r\n执行的脚本如下：\r\n{3}\r\n", DateTime.Now.ToString(), connectionStringBuilder.DataSource + ":" + connectionStringBuilder.InitialCatalog, e.Message, sqlCommand.CommandText);
                    LogWriter(log);
                    if (!sqlExecuteError)
                    {
                        sqlExecuteError = true;
                        if (MessageBox.Show("脚本执行失败，点击'确定'查看详情日志。", "脚本错误", MessageBoxButtons.OKCancel) == System.Windows.Forms.DialogResult.OK)
                        {
                            System.Diagnostics.Process.Start("notepad.exe", "./Log/log.txt");
                        }
                    }
                    multiThread.Stop();
                }
            }
            tenantConnection.Close();
            tenantConnection.Dispose();
            UpdateTextBox(taskIndex);
            SetProcessBar();
        }

        //更新执行情况文本框
        public void UpdateTextBox(int taskIndex)
        {
            SqlConnectionStringBuilder connectionBuilder = new SqlConnectionStringBuilder(TenantConnectStringList[taskIndex - 1]);
            this.BeginInvoke(new Action(() =>
            {
                this.resultList.Items.Add(connectionBuilder.InitialCatalog + "脚本执行完毕！\r\n");
                this.resultList.SelectedIndex = this.resultList.Items.Count - 1;
            }));
        }

        public void SetProcessBar()
        {
            this.BeginInvoke(new Action(() =>
            {
                CompleteTenantCount++;
                this.progressBar.Value = CompleteTenantCount;
            }));
        }

        public void ConfigAdd(string serverName, string serverIp, string password)
        {
            ConfigHelper.AddConfig(serverName, serverIp, password);
            UpdateServerSelector();
        }

        //更新库的选择下拉框
        public void UpdateServerSelector()
        {
            var serverNames = ConfigHelper.GetServerNames();
            this.DbSelector.Items.Clear();
            this.DbSelector.Items.AddRange(serverNames.ToArray());
            if (this.DbSelector.Items.Count == 0)
            {
                this.DbSelector.SelectedIndex = -1;
                this.tenantGridView.DataSource = null;
            }
            else
            {
                this.DbSelector.SelectedIndex = 0;
            }
        }

        /// <summary>
        /// 连接数据库前的网络测试
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <returns>Boolean</returns>
        public bool DbServerConnectTest(string connectionString)
        {
            var connectionStringBuilder = new SqlConnectionStringBuilder(connectionString);
            int port = 1433;
            string ip = connectionStringBuilder.DataSource;
            if (ip.Contains(","))
            {
                ip = ip.Split(',')[0];
                int.TryParse(connectionStringBuilder.DataSource.Split(',')[1], out port);
            }
            if (ip.Contains('\\'))
            {
                ip = ip.Split('\\')[0];
            }
            //测试网络连通性
            IPEndPoint ipEndPoint = new IPEndPoint(IPAddress.Parse(ip), port);
            TcpClient tcpClient = new TcpClient();

            return tcpClient.Connect(ipEndPoint, 5000);
        }

        //选择库，连接AdminCenter，更新Tenant列表
        private void DbSelector_SelectedIndexChanged(object sender, EventArgs e)
        {
            var combox = sender as ComboBox;
            this.btnExam.Enabled = false;           //确定有数据后，测试按钮才有效
            var selectServer = combox.SelectedItem.ToString();
            var adminCenterConnectionStringBuilder = ConfigHelper.GetServerConnectionString(selectServer);
            using (SqlConnection connection = new SqlConnection(adminCenterConnectionStringBuilder.ConnectionString))
            {
                tenantTable = new DataTable();
                //测试网络联通性
                if (DbServerConnectTest(connection.ConnectionString))
                {
                    try
                    {
                        connection.Open();
                        if (connection.State == ConnectionState.Open)
                        {
                            var selectTenantsSql = "select * from dbo.Tenant";
                            SqlCommand selectTenantCmd = new SqlCommand(selectTenantsSql, connection);

                            SqlDataReader reader = selectTenantCmd.ExecuteReader();

                            tenantTable = new DataTable();
                            tenantTable.Load(reader);

                            this.btnExecute.Enabled = false;
                        }
                        else
                        {
                            MessageBox.Show("服务地址连接失败，请检查配置！", "连接失败");
                        }

                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("服务地址连接失败，请检查配置！", "连接失败");
                    }
                    finally
                    {
                        UpdateTenantList();
                    }
                }
                else
                {
                    MessageBox.Show("网络连接错误（网络通畅前提下请检查IP地址或端口号）！", "网络中断");
                    UpdateTenantList();
                }
                if (tenantTable.Rows.Count > 0)
                {
                    this.btnExam.Enabled = true;
                }
            }
        }

        //更新Tenant显示列表
        public void UpdateTenantList()
        {
            var displayTable = new DataTable();
            displayTable.Columns.Add("IsUpdate", typeof(bool));
            displayTable.Columns.Add("TenantName", typeof(string));
            displayTable.Columns.Add("TenantCode", typeof(string));
            displayTable.Columns.Add("TenantServer", typeof(string));
            displayTable.Columns.Add("TestResult", typeof(string));
            displayTable.Columns.Add("ConnectionString", typeof(string));
            displayTable.Columns.Add("State", typeof(bool));
            for (int i = 0; i < tenantTable.Rows.Count; i++)
            {
                var tenantConnectionString = TenantConnectionString(tenantTable.Rows[i]["TenantServer"].ToString(), tenantTable.Rows[i]["TenantCode"].ToString(), tenantTable.Rows[i]["TenantPassword"].ToString());
                displayTable.Rows.Add(true, tenantTable.Rows[i]["TenantName"], tenantTable.Rows[i]["TenantCode"], tenantTable.Rows[i]["TenantServer"], "未连接", tenantConnectionString, false);
            }
            this.tenantGridView.DataSource = displayTable;
        }


        //测试按钮点击事件
        private void btnExam_Click(object sender, EventArgs e)
        {
            SqlConnection tenantConnection = new SqlConnection();
            bool allPast = true;
            this.btnExecute.Enabled = false;
            this.tenantGridView.CellValueChanged -= new System.Windows.Forms.DataGridViewCellEventHandler(this.tenantGridView_CellValueChanged);

            for (int i = 0; i < tenantGridView.Rows.Count; i++)
            {
                DataGridViewCheckBoxCell checkCell = (DataGridViewCheckBoxCell)tenantGridView.Rows[i].Cells["IsUpdate"];
                bool examResult = false;
                if (Convert.ToBoolean(checkCell.Value) == true)
                {
                    tenantConnection.ConnectionString = tenantGridView.Rows[i].Cells["ConnectionString"].Value.ToString();
                    if (DbServerConnectTest(tenantConnection.ConnectionString))
                    {
                        try
                        {
                            tenantConnection.Open();
                            if (tenantConnection.State == ConnectionState.Open)
                            {
                                examResult = true;
                                tenantGridView.Rows[i].Cells["TestResult"].Value = "成功";
                                tenantGridView.Rows[i].Cells["State"].Value = true;
                            }
                            tenantConnection.Close();
                        }
                        catch { }
                    }
                    if (!examResult)
                    {
                        tenantGridView.Rows[i].Cells["TestResult"].Value = "失败";
                        tenantGridView.Rows[i].DefaultCellStyle.BackColor = Color.Red;
                        tenantGridView.Rows[i].DefaultCellStyle.ForeColor = Color.White;
                        allPast = false;
                    }

                }
            }

            this.tenantGridView.CellValueChanged += new System.Windows.Forms.DataGridViewCellEventHandler(this.tenantGridView_CellValueChanged);
            if (allPast)
            {
                this.btnExecute.Enabled = true;
            }
        }

        #region 服务配置增删改
        //添加服务配置
        private void btnAddConfig_Click(object sender, EventArgs e)
        {
            var AddConfigForm = new ConfigForm();
            AddConfigForm.ConfirmEvent += ConfigAdd;
            AddConfigForm.ShowDialog();
        }

        //修改服务配置
        private void btnAlterConfig_Click(object sender, EventArgs e)
        {
            if (this.DbSelector.SelectedItem == null)
            {
                return;
            }
            var serverName = this.DbSelector.SelectedItem.ToString();
            SqlConnectionStringBuilder builder = ConfigHelper.GetServerConnectionString(serverName);
            var address = builder.DataSource;
            var password = builder.Password;

            var AlterConfigForm = new ConfigForm(serverName, address, password);
            AlterConfigForm.ConfirmEvent += ConfigAlter;
            AlterConfigForm.ShowDialog();

        }
        public void ConfigAlter(string serverName, string address, string password)
        {
            ConfigHelper.AlterConfig(serverName, address, password);
            UpdateServerSelector();
        }

        //删除服务配置
        private void btnRemoveConfig_Click(object sender, EventArgs e)
        {
            if (this.DbSelector.SelectedItem == null)
            {
                return;
            }
            var serverName = this.DbSelector.SelectedItem.ToString();
            if (MessageBox.Show("是否确认删除配置？", "删除确认", MessageBoxButtons.OKCancel) == System.Windows.Forms.DialogResult.OK)
            {
                ConfigHelper.RemoveConfig(serverName);
                UpdateServerSelector();
            }
        }
        #endregion

        #region 勾选是否更新的处理

        //手动提交修改，否则勾选框状态修改后要失焦才能触发事件
        private void tenantGridView_CurrentCellDirtyStateChanged(object sender, EventArgs e)
        {
            if (this.tenantGridView.IsCurrentCellDirty)
            {
                this.tenantGridView.CommitEdit(DataGridViewDataErrorContexts.Commit);
            }
        }

        private void tenantGridView_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0)
            {
                DataGridViewCheckBoxCell checkCell = (DataGridViewCheckBoxCell)tenantGridView.Rows[e.RowIndex].Cells["IsUpdate"];
                if (Convert.ToBoolean(checkCell.Value) == true)
                {
                    this.tenantGridView.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.WhiteSmoke;
                    this.tenantGridView.Rows[e.RowIndex].DefaultCellStyle.ForeColor = Color.Black;
                }
                else
                {
                    this.tenantGridView.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.Gray;
                    this.tenantGridView.Rows[e.RowIndex].DefaultCellStyle.ForeColor = Color.White;
                    this.tenantGridView.Rows[e.RowIndex].Cells["TestResult"].Value = "未连接";
                }
            }
        }
        #endregion

        //反选按钮点击事件
        private void toggleAllCheckbox_Click(object sender, EventArgs e)
        {
            foreach (DataGridViewRow item in tenantGridView.Rows)
            {
                item.Cells["IsUpdate"].Value = !Convert.ToBoolean(item.Cells["IsUpdate"].Value);
            }
        }

    }
}
